Create table in SQL with PRIMARY KEY

In this part we will learn Create table in SQL with PRIMARY KEY? So, tables are created in SQL to store and organize data in a structured manner. They serve as containers for storing records, with each record represented as a row and each attribute as a column. Tables allow for efficient data retrieval through SQL queries, enable the establishment of relationships between entities, support data manipulation operations, and provide data security through access control. In summary, tables are fundamental components of a database system that facilitate data storage, organization, retrieval, manipulation, and security.

Here's a simple explanation with a diagram:

In SQL, tables are like organized containers that hold data. They consist of rows and columns, forming a tabular structure.

Columns represent specific attributes or data fields, such as name, age, or address.

Rows represent individual records or instances within the table, with each row holding data corresponding to the columns.

For example, let's say we have a table named "Employee" with columns such as "Employee_ID," "Name," "Age," and "Department." Each row in the table represents a specific employee with corresponding data for each column.

The table above represents an "Employee" table with three rows, each representing an employee and their associated information.

By creating tables in SQL, we can structure and store data in a meaningful way, making it easier to retrieve and manipulate the information based on our needs.

To create a table in SQL, you can use the CREATE TABLE statement. Here's a basic example of how to create a table:

Example

        
          CREATE TABLE TableName (
            column1 datatype,
            column2 datatype,
            column3 datatype,
            ...
          );
        
      

Let's break down the statement:

• CREATE TABLE is the SQL command used to create a new table.
• TableName is the name you choose for your table. Replace it with the desired name.
• column1, column2, column3, and so on, represent the columns in your table. You can specify the column names according to your requirements.
• datatype represents the data type for each column. Examples of data types include INT for integers, VARCHAR for variable-length strings, DATE for dates, and so on.

Here's a more concrete example of creating a table called "Employee" with a few columns

Example

        
          CREATE TABLE Employees (
            EmployeeID INT,
            FirstName VARCHAR(50),
            LastName VARCHAR(50),
            HireDate DATE
          );
        
      

In this example, we have four columns:

• EmployeeID is an integer column.
• FirstName and LastName are both variable-length string columns with a maximum length of 50 characters.

• HireDate is a date column.

You can adjust the column names, data types, and lengths based on your specific needs. Additionally, you can add constraints, such as primary keys or foreign keys, to further define the structure of your table.

You can add constraints to your table definition to enforce data integrity and define relationships between tables. Here's an example of creating a table with constraints:

Example

        
          CREATE TABLE Employees (
            EmployeeID INT PRIMARY KEY,
            FirstName VARCHAR(50),
            LastName VARCHAR(50),
            HireDate DATE
          );
        
      

In this example, the PRIMARY KEY constraint is added to the column1 of the table Table_Name. The PRIMARY KEY constraint ensures that the values in the specified column are unique and not null.

You can customize the constraints based on your specific requirements. 

Other common constraints include FOREIGN KEY,UNIQUE, NOT NULL, and CHECK constraints, which can be added to individual columns. Constraints play an important role in maintaining data integrity and enforcing business rules in a database schema.

Can We use primany key more than one?

In an SQL database table, a primary key is a column or a set of columns that uniquely identifies each row in the table. It ensures the uniqueness and integrity of the data stored in the table. The primary key constraint enforces the following rules:

Uniqueness: Each value in the primary key column(s) must be unique. No two rows can have the same value for the primary key.

Non-nullability: The primary key column(s) cannot have null values. Every row must have a valid value for the primary key.

Immutability: The primary key value(s) should not be changed once they are assigned to a row. Modifying a primary key value is generally not allowed or is highly discouraged.

A primary key is implemented by specifying it when creating or altering a table. In most SQL database management systems (DBMS), you can define a single primary key per table. However, some DBMS may allow composite primary keys, which consist of multiple columns. Composite primary keys are useful when a single column cannot guarantee uniqueness, but the combination of multiple columns can.

To define a primary key in SQL, you typically use the PRIMARY KEY constraint after defining the table columns. Here's an example of creating a table with a primary key:

Example

        

          CREATE TABLE MyTable (
          id INT PRIMARY KEY,
          name VARCHAR(50),
          age INT
          );




      

In this example, the id column is designated as the primary key. It ensures that each row has a unique id value, and the column does not allow null values.

If you want to create a composite primary key, you specify multiple columns within the PRIMARY KEY constraint:

Example

        

          CREATE TABLE MyTable (
          id INT,
          category VARCHAR(50),
          PRIMARY KEY (id, category)
          );




      

In this case, both the id and category columns together form the composite primary key, ensuring the combination of values is unique for each row.